package com.ustcinfo.study.hive.r1.liyang;
import java.sql.*;

/**
 * Created by liyang on 2018/8/3
 **/
public class HiveConnectionTest {
    // hive链接地址
    private static String url = "jdbc:hive2://nodex:10000/default";

    //hive用户名
    private static String userName = "root";

    // hive密码
    private static String passwd = "ly90908";

    // hive 驱动
    private static String driver = "org.apache.hive.jdbc.HiveDriver";

    private static Connection connection = null;
    private static Statement statement = null;
    private static ResultSet resultSet = null;

    // 查询tables sql
    private static String showTableSql = "show tables";

    // 创建内部表dns_liyang sql
    private static String createSql = "create table dns_liyang(ip string,domain_name string,access_time string,target_ip string,status int)row format delimited fields terminated by '#' ";
    // 加载数据到member sql
    private static String localDataSql = "load data local inpath '/home/ustc/data/dnsSample' into table dns_liyang ";

    //统计dns_liyang中表中数据条数
    private static String countSql = "select domain_name,count(*) from dns_liyang group by domain_name";

    private static String isExsitsed ="dns_liyang";



    public static void main(String[] args) throws SQLException {

        //  加载hive驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            System.out.println("驱动加载失败");
            e.printStackTrace();
        }

        try {
            // 连接hive
            connection = DriverManager.getConnection(url, userName, passwd);
            if (connection != null) {
                System.out.println("hive 连接成功");
            } else {
                System.out.println("hive 连接失败");
            }

            statement = connection.createStatement();

            // 获取数据库中所有的表
            resultSet = statement.executeQuery(showTableSql);
            //判断表是否存在；若存在直接显示条数，否则先创建表在，导入数据
            while (resultSet.next()) {
                boolean resultCode=resultSet.getString(1).length()>0?true:false;
                if (resultCode){
                    System.out.println("-----------------表已经创建-------------------");
                    resultSet = statement.executeQuery(countSql);
                    if(resultSet!=null) {
                        resultSet = statement.executeQuery(countSql);
                        System.out.println("正在查询dns_liyang表中数据条数！");
                    }
                    else
                    {   System.out.println("-----表dns_liyang已创建，但无数据正在导入数据！-----");
                        statement.executeQuery(localDataSql);
                        resultSet = statement.executeQuery(countSql);
                    }
                }

                 else{
                    //创建表
                    statement.executeQuery(createSql);
                    //导入数据
                    statement.executeQuery(localDataSql);
                    resultSet = statement.executeQuery(countSql);
                    }

//                while (resultSet.next()) {
////                    System.out.println(resultSet.getInt(1) + "\t" + resultSet.getInt(2));
//                    System.out.println(resultSet.next());
//                }
                while (resultSet.next()) {
                    System.out.println(resultSet.getString(1) + "\t" + resultSet.getInt(2));
                }
            }



        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(1);
        } finally {
            if (statement != null) {
                statement.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
            if(connection != null) {
                connection.close();
            }

        }


    }


}
